/**
* Author: fulishang
* Create Time  : 2016年3月20日,下午3:08:34
* Modify Time :
* Desc  : 
*/
package com.samfu.poi;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.samfu.bean.Customer;

public class ExcelReader {

	private static List<Customer> readExcelData(String filePath) throws Exception{
		
		List<Customer> dataLists = new ArrayList<Customer>();
		FileInputStream fis = new FileInputStream(filePath);
		
		//create workbook
		Workbook workbook = null;
		
		//2007version excel end with xlsx
		if (filePath.toLowerCase().endsWith("xlsx")) {
			workbook = new XSSFWorkbook(fis);
		} else if (filePath.toLowerCase().endsWith("xls")) {
			//2003 version excel end with xls
			workbook = new HSSFWorkbook(fis);
		}
		
		//get the number of sheets in the excel
		int sheetNos = workbook.getNumberOfSheets();
		
		for (int i=0; i<sheetNos; i++) {
			Sheet sheet = workbook.getSheetAt(i);
			Row row;
			Cell cell;
			for (int j=sheet.getFirstRowNum(); j<sheet.getLastRowNum(); j++) {
				Customer customer = new Customer();
				row = sheet.getRow(j);
				for (int k=row.getFirstCellNum(); k<row.getLastCellNum(); k++) {
					cell = row.getCell(k);
					if (k%2==0) {
						customer.setName(cell.toString());
					} else {
						customer.setAddress(cell.toString());
					}
				}
				dataLists.add(customer);
			}
		}
		
		return dataLists;
	}
	
	public static void main(String[] args) throws Exception{
		List<Customer> datas = readExcelData("/Users/fulishang/development/workspace/POIDemo/test4.xlsx");
		
		for(Customer c : datas) {
			System.out.print(c.getName() + " , " + c.getAddress());
			System.out.println();
		}
	}
}
